package com.ncr.teradata.bireport;

import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

/**
 * DBQuery.java
 * 数据库查询类
 *
 * @author ylpeng
 * @date Dec 19, 2006
 *
 * @modify date,auther,desc
 */
public class DBQuery
{
	/**
	 *执行数据库查询语句
	 *
	 *@param sql String 需要执行的SQL语句
	 *@return List 将查询的结果集放入List对象中返回，并关闭数据库连接
	 */
	public List executeToList(String sql){
		Connection conn = DBFactory.getConnection ();
		List list = new ArrayList();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			int colCount = rs.getMetaData().getColumnCount();
			while(rs.next()){
				Object[] row = new Object[colCount];
				for(int i = 0; i < row.length; i++){
					row[i] = rs.getObject(i + 1);
				}
				list.add(row);
			}
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBFactory.close(conn);
		}
		return list;
	}
	
	/**
	 *执行数据库查询语句
	 *
	 *@param sql String 需要执行的SQL语句（带有问号）
	 *@param params String[] SQL语句的参数
	 *@return List 将查询的结果集放入List对象中返回，并关闭数据库连接
	 */
	public List executeToList(String sql,String[] params){
		Connection conn = DBFactory.getConnection();
		List list = new ArrayList();
		try{
			PreparedStatement stmt = conn.prepareStatement(sql);
			for(int i = 1; i <= params.length; i++){
				stmt.setString(i, params[i - 1]);
			}
			ResultSet rs = stmt.executeQuery();
			int colCount = rs.getMetaData().getColumnCount();
			while(rs.next()){
				Object[] row = new Object[colCount];
				for(int i = 0; i < row.length; i++){
					row[i] = rs.getObject(i + 1);
				}
				list.add(row);
			}
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBFactory.close(conn);
		}
		return list;
	}
	
	/**
	 *执行数据库查询语句
	 *
	 *@param sql String 需要执行的SQL语句
	 *@return CachedRowSet 将查询的结果集放入CachedRowSet对象中返回，并关闭数据库连接
	 */
	public CachedRowSet executeToCachedRowSet(String sql){
		Connection conn = DBFactory.getConnection ();
		CachedRowSetImpl rowset = null;
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			rowset = new CachedRowSetImpl();
			rowset.populate(rs);
			rowset.setReadOnly(true);
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBFactory.close(conn);
		}
		return rowset;
	}
	
	/**
	 *执行数据库查询语句
	 *
	 *@param sql String 需要执行的SQL语句（带有问号）
	 *@param params String[] SQL语句的参数
	 *@return CachedRowSet 将查询的结果集放入CachedRowSet对象中返回，并关闭数据库连接
	 */
	public CachedRowSet executeToCachedRowSet(String sql,String[] params){
		Connection conn = DBFactory.getConnection();
		CachedRowSetImpl rowset = null;
		try{
			PreparedStatement stmt = conn.prepareStatement(sql);
			for(int i = 1; i <= params.length; i++){
				stmt.setString(i, params[i - 1]);
			}
			ResultSet rs = stmt.executeQuery();
			rowset = new CachedRowSetImpl();
			rowset.populate(rs);
			rowset.setReadOnly(true);
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBFactory.close(conn);
		}
		return rowset;
	}
	
	/**
	 *执行数据库查询语句，并将结果集封装进ReportData对象中返回
	 *
	 *@param sql String 需要执行的SQL语句
	 *@return ReportData 返回封装了数据库结果集的ReportData对象
	 */
	public ReportData executeToReportData(String sql){
		Connection conn = DBFactory.getConnection();
		ReportData report = new ReportData();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			report.populate(rs);
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBFactory.close(conn);
		}
		return report;
	}
	
	/**
	 *执行数据库查询语句，并将结果集封装进ReportData对象中返回
	 *
	 *@param sql String 需要执行的SQL语句（带有问号）
	 *@param params String[] SQL语句的参数
	 *@return ReportData 返回封装了数据库结果集的ReportData对象
	 */
	public ReportData executeToReportData(String sql,String[] params){
		Connection conn = DBFactory.getConnection();
		ReportData report = new ReportData();
		try{
			PreparedStatement stmt = conn.prepareStatement(sql);
			for(int i = 1; i <= params.length; i++){
				stmt.setString(i, params[i - 1]);
			}
			ResultSet rs = stmt.executeQuery();
			report.populate(rs);
			rs.close();
			stmt.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			DBFactory.close(conn);
		}
		return report;
	}
}